# encoding: utf-8
import time
import pymysql
from pyecharts import Bar,Line,Overlap
# 打开数据库连接
db = pymysql.connect(host="125.222.146.11", port=3306,user="root", passwd="xgxxgx", db="distnsrs",charset='utf8')

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute()  方法执行 SQL 查询
cursor.execute("SELECT DATE_FORMAT( addtime, '%m-%d' ) , COUNT( * ) FROM dist_logs where replace(logtext,'考生号:','') like '18%' GROUP BY DATE_FORMAT( addtime, '%Y-%m-%d' )")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchall()
cursor.execute("SELECT DATE_FORMAT( addtime, '%m-%d' ) , COUNT( distinct (replace(logtext,'考生号:',''))) FROM dist_logs where replace(logtext,'考生号:','') like '18%' GROUP BY DATE_FORMAT( addtime, '%Y-%m-%d' )")

# 使用 fetchone() 方法获取单条数据.
data2 = cursor.fetchall()
print(data2)
dates=[]
count=[]
distcount=[]
for i,x in  enumerate(data):
    dates.append(x[0])
    count.append(x[1])
for i,x in enumerate(data2):
    distcount.append(x[1])
# 关闭数据库连接
db.close()


import datetime
i = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
bar = Bar("大连科技学院微信小程序录取结果查询统计", "数据统计时间："+str(i)+"，数据统计于新生报到微信小程序")
#bar.use_theme('macarons')
bar.add("人次", dates, count,is_more_utils=True,mark_line=["min", "max"],is_label_show=True,xaxis_name_size=24,yaxis_name_size=24)
bar.add("人数", dates, distcount,is_more_utils=True,mark_line=["min", "max"],is_label_show=True,xaxis_name_size=24,yaxis_name_size=24)
line=Line()
line.add("人次", dates, count,is_more_utils=True,mark_line=["min", "max"])
line.add("人数", dates, distcount,is_more_utils=True,mark_line=["min", "max"])
overlap = Overlap(width=1200,height=800)
overlap.add(bar)
overlap.add(line)
overlap.render()

